﻿CREATE PROCEDURE [util].[DropDbConnections]
@DbName [sysname]
AS
BEGIN
	DECLARE @RC int;
	SET @RC = 0;

	DECLARE @ActiveSPID int;
	SELECT @ActiveSPID = [spid]
	FROM master.dbo.sysprocesses
	WHERE ([dbid] = DB_ID(@DbName))

	WHILE(@ActiveSPID IS NOT NULL)
		BEGIN
			PRINT ('Attempting drop of SPID ' + CAST(@ActiveSPID AS varchar) + ' in database ' + @DbName)

			DECLARE @KillCommand nvarchar(20);
			SET @KillCommand = N'KILL ' + CAST(@ActiveSPID AS nvarchar);
			EXEC(@KillCommand); 
			SET @RC = @@ERROR;
			IF(@RC <> 0) GOTO EXIT_PROC;
			SET @ActiveSPID = NULL;

			WAITFOR DELAY '00:00:03';

			SELECT @ActiveSPID = [spid]
			FROM master.dbo.sysprocesses
			WHERE  ([dbid] = DB_ID(@DbName))
		END

EXIT_PROC:
	IF(@RC <> 0)
		RAISERROR('Failed to drop all connections for database %s',16,1, @DbName) WITH LOG;

	RETURN(@RC);
END